package com.shang.core;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class OperExcel extends HttpServlet{

	private static final long serialVersionUID = 6500223670173836343L;

	public void service (HttpServletRequest request,HttpServletResponse response) throws ServletException,java.io.IOException{
		String filePath = request.getParameter("file");
		File srcFile = new File(filePath);
		String outFile = readExcel(srcFile);
		response.getWriter().write(outFile);
//		response.setContentType("application/msexcel;charset=UTF-8");  
//		response.addHeader("Content-Disposition", "attachment;filename=\""+outFile);  
	}

	private String readExcel(File srcFile) {
		
		String fileName = srcFile.getName();
		String suffix = fileName.substring(fileName.lastIndexOf(".")+1).toLowerCase();
		String preffix = fileName.substring(0,fileName.lastIndexOf("."));
		FileInputStream fis = null;
		FileOutputStream fos = null;
		
		SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式
		String date = df.format(new Date());// new Date()为获取当前系统时间，也可使用当前时间戳
		String outFilePath = "D:"+File.separator+preffix+date+".xls";
		
		try {
			fis = new FileInputStream(srcFile);
			Workbook wb = null;
			if(suffix.equals("xls")){
				wb = new HSSFWorkbook(fis);
			}else{
				wb = new XSSFWorkbook(fis);  
			}
			
			Sheet sheet1 = wb.getSheetAt(0);
			Sheet sheet2 = wb.createSheet("拼音");  	//新sheet
			int i = 0;
			for(Row row : sheet1){
				Row row2 = sheet2.createRow(i);  	//新row
				i++;
				int j = 0;
				for(Cell cell : row){
					row2.createCell(j++).setCellValue(PinYinTool.getPingYin(cell.getStringCellValue()+""));
				}
			}
			fos = new FileOutputStream(outFilePath);
			wb.write(fos);
			fos.flush();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return outFilePath;
	}
}
